<?php
/**
 * Created by IntelliJ IDEA.
 * User: Fernando
 * Date: 20/11/2014
 * Time: 08:12
 */

namespace Fiestissima\CRM\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Doctrine\ORM\Query\ResultSetMapping;
use DateTime;
use DateInterval;

class AjaxReportesController extends Controller
{
    public function ventasMesAnteriorAction(Request $request)
    {
        if($request->isXmlHttpRequest())
        {
            //calculo fechas de reportes
            $date = new DateTime(date('Y-m-d'));
            $interval = new DateInterval('P1M');

            $date->sub($interval);

            $month = $date->format('m');
            $year = $date->format('Y');


            //conecto a base y tiro queries
            $em = $this->getDoctrine()->getManager('fiestissima');

            $sql = "select tipo_prod.tipo_nombre as label, sum(ocp.orden_compra_producto_cantidad) as value
from orden_compra_producto as ocp
inner join producto on producto.pr_id = ocp.pr_id
inner join tipo_prod on producto.tipo_id = tipo_prod.tipo_id
inner join orden_compra on orden_compra.orden_compra_id = ocp.orden_compra_id
where year(orden_compra.orden_compra_fecha) = {$year}
and month(orden_compra.orden_compra_fecha) = {$month}
group by tipo_prod.tipo_nombre;
";

            $rsm = new ResultSetMapping();
            $rsm->addScalarResult('label', 'label');
            $rsm->addScalarResult('value', 'value');

            $query = $em->createNativeQuery($sql, $rsm);
            $totales_tipo= $query->getResult();

            $emConfig = $em->getConfiguration();

            $emConfig->addCustomDatetimeFunction('YEAR', 'DoctrineExtensions\Query\Mysql\Year');
            $emConfig->addCustomDatetimeFunction('MONTH', 'DoctrineExtensions\Query\Mysql\Month');
            $emConfig->addCustomDatetimeFunction('DAY', 'DoctrineExtensions\Query\Mysql\Day');



            $sql = "select month(orden_compra_fecha) as mes, sum(orden_compra_contado) as contado, sum(orden_compra_tarjeta) as tarjeta
from orden_compra
where year(orden_compra_fecha) = {$year}
and month(orden_compra_fecha) = {$month}
group by mes
order by mes asc;";

            $rsm = new ResultSetMapping();
            $rsm->addScalarResult('mes', 'mes');
            $rsm->addScalarResult('contado', 'contado');
            $rsm->addScalarResult('tarjeta', 'tarjeta');

            $query = $em->createNativeQuery($sql, $rsm);
            $totales_mes= $query->getResult();

            $response = array();
            $response['totales_mes'] = $totales_mes;
            $response['totales_tipo'] = $totales_tipo;

            return new Response(json_encode($response));
        }
    }
} 